package com.backup.common.utils.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.fastjson.JSONObject;
import com.backup.common.utils.SqlBackUpUtil;
import lombok.extern.slf4j.Slf4j;

import java.io.FileNotFoundException;
import java.util.*;

/**
 * @Description 注意：空白的单元格不会被读取
 * @Author dsf
 * @Date 2020/12/14 15:30
 * @Version V1.0
 **/
@Slf4j
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {

    //insert 字段
    private List<FieldVO> fieldVOList = new ArrayList<>();


    //头部数据
    public LinkedHashMap<Integer, Map<Integer, String>> head = new LinkedHashMap<>();


    /**
     * 解析出的数据
     */
    private LinkedHashMap<Integer,Map<Integer, String>> row2DataMap = new LinkedHashMap<>();
    /**
     * 合并单元格
     */
    private List<CellExtra> extraMergeInfoList = new ArrayList<>();

    public NoModelDataListener(){}

    @Override
    public void invoke(Map<Integer, String> rowData, AnalysisContext analysisContext) {
        //读取到的每行数据,其key是以0开始的索引
        row2DataMap.put(analysisContext.readRowHolder().getRowIndex(),rowData) ;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //所有行都解析完成
        this.explainMergeData(row2DataMap, extraMergeInfoList) ;
    }
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        super.invokeHead(headMap, context);
//        head.put(headMap, head);
//        Map<Integer, String> data = null ;
//        for(Integer key : headMap.keySet()){
//            ReadCellData cellData = headMap.get(key);
//            String value = cellData.getStringValue();
//            data.put(key, value);
//        }
//        head.put(head.size(), data);
    }


    /**
     * 某行的数据解析失败
     * */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        System.err.println("解析失败，但是继续解析下一行: " + exception.getMessage());
        // 如果是某一个单元格的转换异常 能获取到具体行号
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            System.err.println("第{}行，第{}列解析异常" + excelDataConvertException.getRowIndex() +
                    excelDataConvertException.getColumnIndex());
        }
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        switch (extra.getType()){
            case MERGE:{
                extraMergeInfoList.add(extra);
                break;
            }
            case HYPERLINK:{
                break;
            }
            case COMMENT:{
            }
            default: {
            }
        }
    }

    /**
     * 处理合并单元格
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @return 填充好的解析数据
     */
    private void explainMergeData(Map<Integer,Map<Integer, String>> data, List<CellExtra> extraMergeInfoList) {
        //循环所有合并单元格信息
        extraMergeInfoList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex();
            int lastRowIndex = cellExtra.getLastRowIndex();
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();

            Map<Integer,String> rdata = data.get(cellExtra.getRowIndex()) ;
            String val = null ;
            if(rdata != null){
                val = rdata.get(cellExtra.getColumnIndex()) ;
            }
            //遍历每行
            Map<Integer, String> rowData = null ;
            for(int i=firstRowIndex;i<=lastRowIndex;i++){
                rowData = data.get(i) ;
                if(rowData == null){
                    continue;
                }
                for(int c = firstColumnIndex;c<=lastColumnIndex;c++){
                    rowData.put(c,val) ;
                }
            }
        });
    }

    public static void exec(int headLine, NoModelDataListener noModelDataListener, int[] deleteIndex){

//        EasyExcel.read(fileName,new NoModelDataListener())
//                .extraRead(CellExtraTypeEnum.MERGE)  // 需要读取合并单元格信息 默认不读取
//                //.registerConverter(new EmptyConverter()) //默认：DefaultConverterLoader#loadDefaultReadConverter()
//                .ignoreEmptyRow(true)
//                .autoTrim(true)
//                .headRowNumber(1)
//                .autoCloseStream(true)
//                //.sheet("2、功能点拆分表")
//                //.sheet("4、结果计算")
//                .sheet()
//                .doRead();


        //读数据, 替换成自己的地址
        String fileName = "/Users/xxx/Downloads/复杂表头解析.xlsx";

        EasyExcel.read(fileName, noModelDataListener)
                .extraRead(CellExtraTypeEnum.MERGE)
                .sheet()
                .headRowNumber(0)
//                .sheetNo(1)
                .doRead();

        for (int i = 0; i < headLine; i++) {
            noModelDataListener.head.put(i, noModelDataListener.row2DataMap.get(i));
            noModelDataListener.row2DataMap.remove(i);
        }
        noModelDataListener.filedList(headLine, noModelDataListener);
        noModelDataListener.delete(deleteIndex, headLine, noModelDataListener);
    }

    //字段名
    private void filedList(int headLine, NoModelDataListener noModelDataListener){

        int len = noModelDataListener.head.get(0).size();
        for (int l = 0; l < len; l++) {  //列
            String[] f = new String[headLine + 1];
            String[] v = new String[headLine + 1];
            for (int h = 0; h < headLine; h++) { //行
//                System.out.println("第 " + l + "列，数据：" + JSONObject.toJSONString(head.get(l)));
                v[h] = head.get(h).get(l); //创建表的备注
                f[h] = ChineseToSpell.getFirstSpell(v[h]); //创建表的字段
//                f[h] = s;
            }
            f[headLine] = l + "";
            v[headLine] = l + "";
//            String join = String.join("_", f);
//            System.err.println(join + "-----------------------------");

            fieldVOList.add(new FieldVO(String.join("_", f), String.join("_", v)));
        }
    }

    //删除某些字段以及对应的数据
    private void delete(int[] deleteIndex, int headLine, NoModelDataListener noModelDataListener){
        if(null != deleteIndex && deleteIndex.length > 0){
            //有需要删除的索引，也就是说需要跳过一些字段
//            System.out.println(JSONObject.toJSONString(noModelDataListener.fieldVOList));
            int size = noModelDataListener.row2DataMap.size();
            int num = 0;
            int max = size + headLine;
            for (int index : deleteIndex) {
                num++; //list有序集合会重新排序
                noModelDataListener.fieldVOList.remove(index-num);
                for (int i = headLine; i < max; i++) {
                    //删除每一行的字段
                    noModelDataListener.row2DataMap.get(i).remove(index-1);
                }
            }
        }
    }

    public static void main(String[] args) throws FileNotFoundException {
        NoModelDataListener noModelDataListener = new NoModelDataListener();
        NoModelDataListener.exec(4, noModelDataListener, new int[]{2,5,7,12});
//        System.out.println(noModelDataListener.head);
//        System.out.println(noModelDataListener.row2DataMap);
//        System.out.println(JSONObject.toJSONString(noModelDataListener.fieldVOList));
//
//        System.out.println(SqlBackUpUtil.joinCreateTableSql("test", noModelDataListener.fieldVOList));
        List<String> list = SqlBackUpUtil.insertSql("test", 4, noModelDataListener.fieldVOList, noModelDataListener.row2DataMap);
        System.out.println(list);
    }
}
